package edu.unc.comp410.colemangeer.data;

import static java.lang.System.err;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashSet;
import java.util.Set;

import org.apache.log4j.Logger;

import edu.unc.comp410.colemangeer.model.Customer;
import edu.unc.comp410.sharedcode.SQLutilities;
import edu.unc.comp410.sharedcode.ValidateUtilities;



/**
 */
public class CustomerGateway implements ICustomerGateway {

		// Data members.
	private Connection conn;
	private Statement stmt;
	private String DB; // Useful for debugging.
	private String MACH; // Useful for debugging.
	private static Logger log = Logger.getLogger( CustomerGateway.class );
	private static Integer nextUnusedId = 1000;
	
	/** 
	 * Default constructor
	 */
	public CustomerGateway( String db, String mach ) 
	throws ClassNotFoundException {
		ValidateUtilities.checkForContent( db );
		ValidateUtilities.checkForContent( mach );
		DB = db;
		MACH = mach;
		conn = SQLutilities.getConnection( db, mach );
		stmt = SQLutilities.getStatement( conn );
	}
	
	/**
	 * @requires theRS.next() has been executed and there is another
	 *    row to process
	 * @param theRS
	 * @return
	 */
	private Customer 
	db2Customer( ResultSet theRS ) {
		Customer result = null;
		try {
			Integer CustomerId = theRS.getInt( "Id" );
			String Name = theRS.getString( "Name" );
			String StreetAddress = theRS.getString( "StreetAddress" );
			String City = theRS.getString("City");
			String StateAbbrev = theRS.getString("StateAbbrev");
			String ZipCode = theRS.getString( "ZipCode" );
			String areaCodeString = theRS.getString( "AreaCode" );
			Integer areaCode = Integer.parseInt( areaCodeString );
			String phoneNumber = theRS.getString( "PhoneNum" );
			result = new Customer( CustomerId, Name, StreetAddress
				, City, StateAbbrev
				, ZipCode, areaCode, phoneNumber );
		} catch( SQLException e ) {
			System.err.println( e );
			e.printStackTrace();
		} 
		return result;
	}

	/**
	 * Search the Customer table of the DB for the single entry
	 *    with CustomerID = searchID.
	 * @param searchID = CustomerID to search DB for.
	 * @return the single Customer entry with CustomerID = searchID,
	 *    null if no match
	 */
	public Customer 
	getById( Integer searchID ) {		
		Customer result = null;
			// Search DB.
		final String query = "SELECT Id, Name, StreetAddress, Zipcode, "
			+ " AreaCode, PhoneNum "
			+ "FROM Customer WHERE Id = " + searchID;
		log.debug( "CustomerGateway: findById: query = " + query );
		try {
			ResultSet rs = stmt.executeQuery( query );
			if( rs.next() ) result = db2Customer( rs );
		} catch( SQLException e ) {
			err.println( e );
			e.printStackTrace();
		}
		return result;
	}
	
	/**
	 * Search the Customer table of the DB for all entries
	 * that contain nameSubstring as a substring in the Name field.
	 * @param nameSubstring
	 * @return all Customer entries that contain nameSubstring
	 *     as a substring in the Name field.
	 */
	public Set<Customer> 
	getByName( String nameSubstring ) { 
		Set<Customer> result = new HashSet<Customer>();
		String query = "SELECT Id, Name, StreetAddress,City, StateAbbrev  "
			+ ", ZipCode, AreaCode, PhoneNumber "
			+ "FROM Customer WHERE Name LIKE '%"+ nameSubstring + "%'";
		log.debug( query );
		try {
			ResultSet rs = stmt.executeQuery( query );
			while( rs.next() ) {
				Customer c = db2Customer( rs );
				log.debug( "CustomerGateway.getByName: c = " + c );
				result.add( c );
			}
		} catch (SQLException e) {
			err.println( e );
			e.printStackTrace();
		}
		return result;
	}
	
	
	public Integer getNextUnusedId() {
		return nextUnusedId++;
	}
	
//	public String getDB() { return DB; }
//	public String getMACH() { return MACH; }
//	
//	/**
//	 * We were successful in obtaining both a DB
//	 * connection and a DB statement?
//	 */
	public boolean successfulDBconnection() {
		return( null != conn && null != stmt );
	}
	
	/**
	 * 
	 */
	@Override public String 
	toString() {
		String result = "CustomerGateway: db = " + DB
			+ " machine = " + MACH;
		return result;
	}
		
	/**
	 * Too dangerous to have a clear that really
	 * does wipe out the DB table.
	 */
	public void clear() {
//		final String update = "DELETE FROM Customer";
//		log.debug( update );
//		int rowsDeleted = 0;
//		try {
//			rowsDeleted = stmt.executeUpdate( update );
//		} catch (SQLException e) {
//			err.println( e );
//			e.printStackTrace();
//		}
	}	
	
	/**
	 * Delete one entry
	 * @param custId
	 * @return number of rows delete (should always be 0 or 1)
	 */
	public int 
	deleteById( int custId ) {
		final String update = "DELETE FROM Customer WHERE Id = "
			+ custId;
		log.debug( update );
		int result = 0;
		try {
			result = stmt.executeUpdate( update );
		} catch (SQLException e) {
			err.println( e );
			e.printStackTrace();
		}
		return result;
	}

	public int
	size() {
		int result = 0;
		final String query = "SELECT COUNT(*) AS Size FROM Customer";
		log.debug( "size: query = " + query );
		try {
			ResultSet rs = stmt.executeQuery( query );
			if( rs.next() ) {
				result = rs.getInt( "Size" );
			}
		} catch (SQLException e) {
			err.println( e );
			e.printStackTrace();
		}
		return result;
	}
//	
//	/**
//	 * INSERT INTO Customer VALUES (1,'Kye', '', 501', 201', 666-1212' )
//	 */
//	public void
//	insert( Customer theCustomer ) {
//		String insert = "INSERT INTO Customer VALUES ("
//			+ theCustomer.getCustID() + ",'"
//			+ theCustomer.getName() + "', '"
//			+ theCustomer.getStreetAddress() + "', "
//			+ theCustomer.getZipcode() + ", "
//			+ theCustomer.getAreaCode() + ", '"
//			+ theCustomer.getPhoneNumber() + "' "
//			
//				// Version info
//			//+ ",0,'',''"
//			+ ")";
//		log.debug( insert );
//		int result = 0;
//		try {
//			result = stmt.executeUpdate( insert );
//		} catch (SQLException e) {
//			err.println( e );
//			e.printStackTrace();
//		}
//	}

}
